﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace App_1Mark1
{
    public partial class frm_P_Examen : Form
    {
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        SqlCommand sqlCom = new SqlCommand();
        SqlConnection sqlC = new SqlConnection();
        SqlCommandBuilder sqlCB = new SqlCommandBuilder();
        DataSet DS = new DataSet();

        public frm_P_Examen()
        {
            InitializeComponent();
        }

        private void frm_P_Examen_Load(object sender, EventArgs e)
        {
            cbYear.SelectedIndexChanged -= cbYear_SelectedIndexChanged;
            cbFiliere.SelectedIndexChanged -= cbFiliere_SelectedIndexChanged;
            dtpHE.CustomFormat = "hh:mm";
            dtpHE.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            dtpHE.ShowUpDown = true;
            dtpDE.CustomFormat = "dd/MM/yyyy";
            dtpDE.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            dtpDE.ShowUpDown = true;
            cbYear.Items.Add("2016");
            cbYear.Items.Add("2017");
            cbYear.SelectedIndex = 0;
            sqlCom.Connection = sqlC;
            sqlC.ConnectionString = "Data Source=DESKTOP-03S1896;Initial Catalog=Gestion_Scolarité;Integrated Security=True";
            sqlC.Open();
            fill_cb_Filiere();
            fill_cb_Module();
            fill_cb_Salle();
            fillGrid();
            cbFiliere.SelectedIndexChanged += cbFiliere_SelectedIndexChanged;
            cbYear.SelectedIndexChanged += cbYear_SelectedIndexChanged;
        }

        void fill_cb_Filiere()
        {
            sqlCom.CommandText = "select * from Filiere";
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Filiere");
            cbFiliere.DataSource = DS.Tables["Filiere"];
            cbFiliere.DisplayMember = "Nom_Filiere";
            cbFiliere.ValueMember = "ID_Filiere";
        }

        void fill_cb_Module()
        {
            sqlCom.CommandText = "select M.ID_Module, M.NOM_Module from Module M inner join Contenir C on M.ID_Module = C.ID_Module inner join Filiere F on F.ID_Filiere = C.ID_Filiere where F.ID_Filiere = " + cbFiliere.SelectedValue.ToString();
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Module");
            cbModule.DataSource = DS.Tables["Module"];
            cbModule.DisplayMember = "NOM_Module";
            cbModule.ValueMember = "ID_Module";
        }

        void fill_cb_Salle()
        {
            sqlCom.CommandText = "select * from Salle";
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Salle");
            cbSalle.DataSource = DS.Tables["Salle"];
            cbSalle.DisplayMember = "Type_Salle";
            cbSalle.ValueMember = "ID_Salle";
        }

        void fillGrid()
        {
            sqlCom.CommandText = "select NOM_Module,Date_Examen,Heure_Examen,Type_Salle from Planification_Examen PE inner join Module M on PE.ID_Module = M.ID_Module inner join Salle S on PE.ID_Salle = S.ID_Salle where Annee_Scolaire = " + cbYear.SelectedItem.ToString();
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Planification_Examen");
            dgvE.DataSource = DS.Tables["Planification_Examen"];
        }

        private void cbFiliere_SelectedIndexChanged(object sender, EventArgs e)
        {
            DS.Tables["Module"].Clear();
            fill_cb_Module();
        }

        private void lblOK_Click(object sender, EventArgs e)
        {
            sqlCom.CommandText = "insert into Planification_Examen values(" + cbModule.SelectedValue.ToString() + ", " + cbSalle.SelectedValue.ToString() + ", null, '" + dtpDE.Value.Month.ToString() + "/" + dtpDE.Value.Day.ToString() + "/" + dtpDE.Value.Year.ToString() + "', '" + dtpHE.Value.Hour.ToString() + ":" + dtpHE.Value.Minute.ToString() + "', " + cbYear.SelectedItem.ToString() + ")";
            sqlCom.ExecuteNonQuery();
            DS.Tables["Planification_Examen"].Clear();
            fillGrid();
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            DS.Tables["Planification_Examen"].Clear();
            fillGrid();
        }
    }
}
